
package cn.hpclub.server.controller.admin.spreadmember;

import cn.hpclub.server.constant.Constant;
import cn.hpclub.server.model.*;
import cn.hpclub.server.model.Package;
import cn.hpclub.server.model.product.Product;
import cn.hpclub.server.util.DateKit;
import cn.hpclub.server.util.InterfaceUtil;
import cn.hpclub.server.util.QueryHelper;
import com.alibaba.fastjson.JSONArray;
import com.jfinal.kit.JsonKit;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.*;
import java.util.Map.Entry;
import java.util.concurrent.ConcurrentHashMap;

/**
 * @FileName: PromotionService.java
 * @Author zhl
 * @Description:首页弹窗信息Service
 * @Date 2016年6月27日 下午2:38:57
 * @CopyRight ZTE Corporation
 */
public class SpreadMemberAuthConfigService {

    public static final String              OPERATION_ADD        = "add";
    public static final String              OPERATION_EDIT       = "edit";

    public static final int                 TYPE_CHANNEL_TYPE    = 6;
    private static final Logger             logger               = LoggerFactory
            .getLogger(SpreadMemberAuthConfigService.class);
    public static SpreadMemberAuthConfigService service              = new SpreadMemberAuthConfigService();

    /**
     * 
     * @Title: getSqlSelect
     * @Description:取已授权业务数据列表时封装脚本时用，注意：业务授权和泛店员业务授权功能模块都使用了该方法，若有修改，请注意单独再写方法
     * @return String
     */
    public String getSqlSelect(){
        String selectSql = "SELECT * ";
        return selectSql;
    }

    public String getSqlExceptSelect(ConcurrentHashMap<String, String> searchInfo){
        String sqlExceptSelect = "FROM (SELECT sys_code.`NAME` AS type_name,t. name,o.DisplayName,bac.creator_org_id,"
                + "bac.config_business_id,bac.config_business FROM business_auth_config bac "
                + " LEFT JOIN tariff t ON bac.config_business_id = t.id" +
                // " LEFT JOIN tariff_class_relation tcr ON t.id=tcr.tariff_id "
                // +
                // " LEFT JOIN tariff_class tc ON tcr.tariff_class_id=tc.id "+
        "  LEFT JOIN( SELECT sv. NAME,sv.VALUE FROM sys_code_value sv "
                + " LEFT JOIN sys_code_type st ON st.id = sv.type_id WHERE st.`code` = 'HDLX' AND st.is_deleted = 0"
                + " AND sv.is_deleted = 0) as sys_code ON bac.config_business=sys_code.`VALUE` "
                + " LEFT JOIN organization o ON bac.creator_org_id=o.id  WHERE 1=1 "
                + " AND t.enabled=1 AND t.is_deleted=0 " + " AND t.end_date >= CURDATE()";
        // " AND tc.enabled=1

        for(Entry<String, String> item : searchInfo.entrySet()){
            sqlExceptSelect += buildQuery(item);
        }
        sqlExceptSelect += " GROUP BY bac.config_business_id,bac.creator_org_id) as table_s";
        return sqlExceptSelect;
    }

    /**
     * 
     * @Title: buildQuery
     * @Description:业务授权中获取或者查询已授权列表时用到的查询条件封装
     * @param item
     * @return String
     */
    private String buildQuery(Entry<String, String> item){
        if("list_select_type".equalsIgnoreCase(item.getKey())){
            return " and bac.config_business =" + item.getValue();
        }
        if("org_id".equalsIgnoreCase(item.getKey())){
            return " and bac.creator_org_id in("
                    + InterfaceUtil.getChlidOrgIdSql(Integer.parseInt(item.getValue()), Constant.MAX_ORG_LEVEL, true)
                    + ") ";
        }
        if("name".equalsIgnoreCase(item.getKey()) && StrKit.notBlank(item.getValue())){
            return " and t.name like '%" + item.getValue() + "%'";
        }
        return " ";
    }

    public Page<Record> getChannel(int type, int pageNumber, int pageSize, ConcurrentHashMap<String, String> map){
        QueryHelper helper = new QueryHelper("select *", Constant.Table.ORGANIZATION_CLASS, "oc");
        helper.addCondition("oc.level=?", type - 3);// type-3正好是其层级
        String exact = map.get("queryType");
        helper.addCondition(map.containsKey("keyword") && !"exact".equals(exact),
                            "(oc.class_name like ? or oc.WebsiteId LIKE ?)", "%" + map.get("keyword") + "%",
                            "%" + map.get("keyword") + "%");
        helper.addCondition(map.containsKey("keyword") && "exact".equals(exact), "(oc.class_name= ? or oc.WebsiteId=?)",
                            map.get("keyword"), map.get("keyword"));
        Page<Record> page = Db.paginate(pageNumber, pageSize, helper.getSelect(), helper.getSqlExceptSelect());
        return page;
    }

    public Page<Record> getCity(int pageNumber, int pageSize, ConcurrentHashMap<String, String> map, String operation,
            int configBusinessId, int creatorOrgId, String flowId){
        QueryHelper helper = new QueryHelper("SELECT *", Constant.Table.ORGANIZATION, "org");
        helper.addCondition("org.CityCode=org.id ");
        helper.addCondition("org.id in " + InterfaceUtil.getChlidOrgIdSql(InterfaceUtil.getOrgId(), 1, true));

        if(OPERATION_EDIT.equalsIgnoreCase(operation)){
            helper.addCondition("org.id not in(select config_type_id from spread_member_auth_config WHERE config_type=1 AND config_business_id=? and creator_org_id=? ) ",
                                configBusinessId, creatorOrgId);
        } else if(OPERATION_ADD.equalsIgnoreCase(operation)){
            helper.addCondition("org.id not in(SELECT config_type_id from business_auth_config_temp con WHERE config_type=1 AND flow_id=?)",
                                flowId);
        }
        String exact = map.get("queryType");
        helper.addCondition(map.containsKey("keyword") && !"exact".equals(exact), "(org.DisplayName LIKE ?)",
                            "%" + map.get("keyword") + "%");
        helper.addCondition(map.containsKey("keyword") && "exact".equals(exact), "(org.DisplayName = ?)",
                            map.get("keyword"));
        Page<Record> page = Db.paginate(pageNumber, pageSize, helper.getSelect(), helper.getSqlExceptSelect());
        return page;
    }


    public Page<Record> getMemberSpreader(int pageNumber, int pageSize, ConcurrentHashMap<String, String> map){
        QueryHelper helper = new QueryHelper("select mo.id,mo.name,mo.emp_code,mo.mobile", Constant.Table.MEMBER_ORG,
                "mo");
        String orgFilter = InterfaceUtil.getChlidOrgIdSql(InterfaceUtil.getOrgId(), Constant.MAX_ORG_LEVEL, true);
        helper.addCondition("mo.org_id IN" + orgFilter);
        helper.addCondition("mo.enabled=1");
        helper.addCondition("mo.is_deleted=0");
        helper.addCondition("mo.member_type=3");
        helper.addCondition(map.containsKey("keyword"), "(mo.name like ? or mo.user_name =? or mo.mobile=?)",
                "%" + map.get("keyword") + "%", map.get("keyword"), map.get("keyword"));
        Page<Record> page = Db.paginate(pageNumber, pageSize, helper.getSelect(), helper.getSqlExceptSelect());
        return page;
    }



    public Page<Record> queryChannelTypeTemp(int pageNumber, int pageSize, ConcurrentHashMap<String, String> map,
            String flowId){
        QueryHelper helper = new QueryHelper("select bact.id,oc.WebsiteId,oc.class_name,o.DisplayName ", " business_auth_config_temp  bact " +
                " LEFT JOIN organization_class oc ON bact.config_type_id=oc.id" +
                " LEFT JOIN organization o ON bact.city_org_id=o.id ");
        helper.addCondition("bact.config_type=? AND bact.flow_id=?",
                            SpreadMemberAuthConfigService.TYPE_CHANNEL_TYPE, flowId);
        helper.addCondition(map.containsKey("keywordChannelType"), "(oc.class_name LIKE ? OR oc.WebsiteId=? OR o.DisplayName LIKE ?)",
                            "%" + map.get("keywordChannelType") + "%", map.get("keywordChannelType"),"%" + map.get("keywordChannelType") + "%");
        Page<Record> page = Db.paginate(pageNumber, pageSize, helper.getSelect(), helper.getSqlExceptSelect());
        return page;
    }

    /**
     * 保存到临时表
     * 
     * @param type
     * @param flow_id
     * @param ids
     * @return
     */
    public boolean saveByTypeTemp(String flow_id, int type, Integer[] ids,Integer[] cityOrgIds){
        StringBuffer sb = new StringBuffer();
        sb.append("INSERT INTO " + Constant.Table.TABLE_BUSINESS_AUTH_CONFIG_TEMP + " (" + BusinessAuthConfig.FLOW_ID
                + "," + BusinessAuthConfig.CONFIG_TYPE + "," + BusinessAuthConfig.CONFIG_TYPE_ID + ","+BusinessAuthConfig.CITY_ORG_ID+") VALUES(?,?,?,?)");
        for(int id : ids){
            // 查询数据是否已存在，存在就直接跳过
            String selectExistData = "select * from " + Constant.Table.TABLE_BUSINESS_AUTH_CONFIG_TEMP
                    + " WHERE flow_id=? AND config_type=?" + " AND config_type_id=? AND city_org_id=?";

            for (Integer cityOrgId : cityOrgIds) {
                Record record = Db.findFirst(selectExistData, flow_id, type, id,cityOrgId);
                if((record != null && record.getInt("id") > 0) || cityOrgId==-1){
                    continue;
                }
                Db.update(sb.toString(), flow_id, type, id,cityOrgId);
            }
        }
        return true;
    }

    /**
     * 根据业务类型和配置类型获取配置数据
     * 
     * @param business
     * @param type
     * @return
     */
    public List<Record> getConfigByBusiness(int business, int type){
        return Db.find("SELECT * FROM " + Constant.Table.TABLE_BUSINESS_AUTH_CONFIG + " WHERE "
                + BusinessAuthConfig.CONFIG_TYPE + "=" + type + " AND " + BusinessAuthConfig.CREATOR_ORG_ID + "="
                + InterfaceUtil.getOrgId() + " AND " + BusinessAuthConfig.CONFIG_BUSINESS + "=?", business);
    }

    public int deleteAllMemberForSpreadAuthConfig(int type, int configBusiness, int configBusinessId,
            int creatorOrgId){
        String deleteSql = "delete from spread_member_auth_config where config_type=? AND config_business=? "
                + "AND config_business_id=? AND creator_org_id=?";
        return Db.update(deleteSql, type, configBusiness, configBusinessId, creatorOrgId);
    }

    public int deleteAllMemberForBusinessAuthConfigTemp(int type, String flowId){
        String deleteSql = "delete from business_auth_config_temp where flow_id=? AND config_type=? ";
        return Db.update(deleteSql, flowId, type);
    }

    /**
     * @Title: getSpreadMemberChannelTypePagerForRegular
     * @Description:TODO
     * @param pageNumber
     * @param pageSize
     * @param config_business_id
     * @param creator_org_id
     * @param map
     * @return Page<Record>
     */
    public Page<Record> getSpreadMemberChannelTypePagerForRegular(int pageNumber, int pageSize, int config_business_id,
            int creator_org_id, ConcurrentHashMap<String, String> map,int config_business){
        QueryHelper helper = new QueryHelper(" SELECT oc.WebsiteId,oc.class_name,bac.id,o.DisplayName",
                Constant.Table.TABLE_SPREAD_MEMBER_AUTH_CONFIG + " bac  LEFT JOIN  "
                        + " organization_class oc ON bac.config_type_id=oc.id  LEFT JOIN organization o ON bac.city_org_id=o.id ");
        helper.addCondition("bac.config_business_id=? and bac.creator_org_id=? and bac.config_type=6 AND bac.config_business=? AND bac.city_org_id IN "+InterfaceUtil.getChlidOrgIdSql(InterfaceUtil.getOrgId(),Constant.MAX_ORG_LEVEL,false),
                config_business_id, creator_org_id,config_business);
        helper.addCondition(map.containsKey("keywordChannelType"), " (oc.WebsiteId = ? OR oc.class_name LIKE ? OR o.DisplayName LIKE ? )",
                map.get("keywordChannelType"), "%" + map.get("keywordChannelType") + "%", "%" + map.get("keywordChannelType") + "%");
        Page<Record> page = Db.paginate(pageNumber, pageSize, helper.getSelect(), helper.getSqlExceptSelect());
        return page;
    }



    /**
     * @Title: saveSpreadMemberByType
     * @Description:泛店员业务授权:编辑时新增渠道（小类）授权配置
     * @param type
     * @param ids
     * @param business
     * @param businessId
     * @param creatorOrgId
     * @return boolean
     */
    public boolean saveSpreadMemberByType(int type, Integer[] ids,Integer[] cityOrgIds, int business, int businessId, int creatorOrgId){
        StringBuffer sb = new StringBuffer();
        Date now = new Date();
        sb.append("INSERT INTO " + Constant.Table.TABLE_SPREAD_MEMBER_AUTH_CONFIG + " ("
                + BusinessAuthConfig.CONFIG_TYPE + "," + BusinessAuthConfig.CONFIG_TYPE_ID + ","
                + BusinessAuthConfig.CONFIG_BUSINESS + "," + "" + BusinessAuthConfig.CONFIG_BUSINESS_ID + ","
                + BusinessAuthConfig.CREATOR_ORG_ID + "," + BusinessAuthConfig.UPDATED_TIME + "," + BusinessAuthConfig.CITY_ORG_ID +") VALUES(?,?,?,?,?,?,?)");
        for(int id : ids){
            // 查询数据是否已存在，存在就直接跳过
            String selectExistData = "select * from " + Constant.Table.TABLE_SPREAD_MEMBER_AUTH_CONFIG
                    + " WHERE config_type=? AND config_type_id=?"
                    + " AND config_business=? AND config_business_id=? AND creator_org_id=? AND city_org_id=?";

            for (Integer cityOrgId : cityOrgIds) {
                Record record = Db.findFirst(selectExistData, type, id, business, businessId,creatorOrgId, cityOrgId);
                if((record != null && record.getInt("id") > 0) || cityOrgId==-1){
                    continue;
                }
                Db.update(sb.toString(), type, id, business, businessId, creatorOrgId, now,cityOrgId);
            }

        }
        return true;
    }

    public Page getBusinessAuthListByPage(int pageNumber,int pageSize,ConcurrentHashMap<String, String> searchInfo,int businessType){
        int thisPage = (pageNumber - 1) * pageSize;
        String sqlExceptSelect = "SELECT * FROM (";
        String countSql = "SELECT count(*) FROM (";

        if(businessType >= 0){// 根据传入业务类型获取相关业务数据
            sqlExceptSelect += getSqlExceptSelectForOneBusiness(searchInfo, businessType);
            countSql += getCountSqlExceptSelectForOneBusiness(searchInfo, businessType);
        }

        sqlExceptSelect += ") AS table_s ORDER BY creator_org_id ASC,updated_time DESC";
        countSql += ") AS table_s";



        sqlExceptSelect += " limit " + thisPage + "," + pageSize;

        List<Record> list = Db.find(sqlExceptSelect.toString());
        List<Map<String, Object>> dataList = new ArrayList<>();
        Map<String,String> businessTypeMap = SpreadMemberAuthConfigService.service.getBusinessTypeName(businessType);
        list.forEach(record -> {
            Map<String, Object> map = new HashMap<>();
            map.put("name", record.getStr("name"));
            map.put("DisplayName", record.getStr("DisplayName"));
            map.put("creator_org_id", record.getInt("creator_org_id"));
            map.put("config_business_id", record.getInt("config_business_id"));
            map.put("updated_time", record.getDate("updated_time") != null
                    ? DateKit.toStr(record.getDate("updated_time"), "yyyy-MM-dd HH:ss:mm") : "1970-01-01 23:59:59");

            int config_business = record.getInt("config_business");
            map.put("config_business", config_business);
            if(businessTypeMap.containsKey(config_business+"")){
                map.put("type_name", businessTypeMap.get(config_business+""));
            }
            dataList.add(map);
        });

        long totalRow = Db.queryLong(countSql.toString());
        int totalPage = (int)(totalRow / (long)pageSize);
        if(totalRow % (long)pageSize != 0L){
            ++totalPage;
        }
        Page page = new Page(dataList, pageNumber, pageSize, totalPage, (int)totalRow);
        return page;
    }



    public Map<String,String> getBusinessTypeName(int businessType){
        Map<String,String> map = new HashMap<>();
        String sql = " SELECT sv.name,sv.value FROM sys_code_value sv LEFT JOIN sys_code_type st ON st.id = sv.type_id WHERE st.code = '"
                + getBusinessTypeCode(businessType) + "' AND st.is_deleted = 0 AND sv.is_deleted = 0 ";
        List<Record> list = Db.find(sql);
        if(list!=null && list.size()>0){
            list.forEach(r->{
                map.put(r.getStr("value"),r.getStr("name"));
            });
        }
        return map;
    }

    /**
     * @param searchInfo
     * @Title: getSqlExceptSelectForOneBusiness
     * @Description:TODO
     * @param businessType
     * @return String
     */
    private String getSqlExceptSelectForOneBusiness(ConcurrentHashMap<String, String> searchInfo, int businessType){
        String sqlExceptSelect = "SELECT bus_temp.name, o.DisplayName, " +
                "bac.creator_org_id, bac.config_business_id, bac.config_business, bac.updated_time " +
                "FROM spread_member_auth_config bac " +
                " LEFT JOIN organization o ON bac.creator_org_id = o.id LEFT JOIN ";

        sqlExceptSelect += getSqlExceptSelectByBusinessType(businessType);

        for(Entry<String, String> item : searchInfo.entrySet()){
            sqlExceptSelect += buildQueryForSpreadMemberBusiness(item, businessType);
        }
        sqlExceptSelect += " GROUP BY bac.config_business_id, bac.creator_org_id";
        return sqlExceptSelect;
    }
    /**
     * @param searchInfo
     * @Title: getSqlExceptSelectForOneBusiness
     * @Description:TODO
     * @param businessType
     * @return String
     */
    private String getCountSqlExceptSelectForOneBusiness(ConcurrentHashMap<String, String> searchInfo, int businessType){
        String sqlExceptSelect = "SELECT bac.updated_time " +
                "FROM spread_member_auth_config bac " +
                " LEFT JOIN ";
        sqlExceptSelect += getSqlExceptSelectByBusinessType(businessType);
        for(Entry<String, String> item : searchInfo.entrySet()){
            sqlExceptSelect += buildQueryForSpreadMemberBusiness(item, businessType);
        }
        sqlExceptSelect += " GROUP BY bac.config_business_id, bac.creator_org_id";
        return sqlExceptSelect;
    }



    /**
     * @Title: getSqlExceptSelectByBusinessType
     * @Description:TODO
     * @param businessType
     * @return String
     */
    private String getSqlExceptSelectByBusinessType(int businessType){
        String sql = "";
        if(businessType < Constant.SM_BUSINESS_TYPE_START){
            sql = Tariff.dao.getSqlExceptSelect();// 资费类业务
        } else{
            switch(businessType){
                case Constant.SpreadMemberAuthConfigBusinessType.ZDXS:// 终端销售
                    sql = Product.dao.getSqlExceptSelect();
                    break;
                case Constant.SpreadMemberAuthConfigBusinessType.KDYY:// 大市场宽带预约
                    sql = Broadband.dao.getSqlExceptSelect(Broadband.BD_SCOPE_YY);
                    break;
                case Constant.SpreadMemberAuthConfigBusinessType.KDKH:// 大市场宽带开户
                    sql = Broadband.dao.getSqlExceptSelect(Broadband.BD_SCOPE_KH);
                    break;
                case Constant.SpreadMemberAuthConfigBusinessType.XHRW:// 选号入网，代表全号段选号入网和选号入网两种业务，因为客户端取这两种业务套餐时的传参一模一样
                    sql = Package.dao.getSqlExceptSelect(Package.PG_TYPE_XHRW);
                    break;
                case Constant.SpreadMemberAuthConfigBusinessType.YXHDXHRW:// 营销活动选号入网
                    sql = Package.dao.getSqlExceptSelect(Package.PG_TYPE_PH_YXHDXHRW);
                    break;
                default:
                    break;
            }
        }

        return sql;
    }

    /**
     * @Title: getBusinessTypeCode
     * @Description:TODO
     * @param businessType
     * @return String
     */
    private String getBusinessTypeCode(int businessType){
        if(businessType < Constant.SM_BUSINESS_TYPE_START){
            return Constant.ACTIVITY_TYPE_SYS_CODE;
        } else{
            return Constant.SPREAD_MEMBER_BUS_TYPE_SYS_CODE;
        }
    }

    /**
     * @Title: buildQueryForSpreadMemberBusiness
     * @Description:TODO
     * @param item
     * @param businessType
     * @return String
     */
    private String buildQueryForSpreadMemberBusiness(Entry<String, String> item, int businessType){
        if("list_select_type".equalsIgnoreCase(item.getKey())){
            return " AND bac.config_business =" + item.getValue();
        }
        if("org_id".equalsIgnoreCase(item.getKey())){
            return " AND bac.creator_org_id in("
                    + InterfaceUtil.getChlidOrgIdSql(Integer.parseInt(item.getValue()), Constant.MAX_ORG_LEVEL, true)
                    + ") ";
        }
        if("name".equalsIgnoreCase(item.getKey()) && StrKit.notBlank(item.getValue())){
            return " AND bus_temp.name like '%" + item.getValue() + "%'";
        }

        return " ";
    }


}
